﻿using System;
using System.Data;
using System.Collections.Generic;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TrainingHistoryDAL : ITrainingHistoryDAL
    {
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable Search(int? intTrainHisId, string strQueryString, int? intCreateBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@TrainHisId", intTrainHisId);
                dbManager.AddParameters(1, "@emp_seq", strQueryString);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT TrainHisId, emp_seq, StartDate, EndDate, CouseName "
                        + " , InstituteName, LocationName, Remark"    
                        +" FROM hrmtraininghistory htm"
                        + " WHERE htm.emp_seq = @emp_seq AND RecordStatus not like @RecordStatus";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert(string strQueryString, string strCouseName, string strInstituteName, string strLocationName
            , Nullable<DateTime> strStartDate, Nullable<DateTime> strEndDate, string strRemark, int? intCreateBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@emp_seq", strQueryString);
                dbManager.AddParameters(1, "@CouseName", strCouseName);
                dbManager.AddParameters(2, "@InstituteName", strInstituteName);
                dbManager.AddParameters(3, "@LocationName", strLocationName);
                dbManager.AddParameters(4, "@StartDate", strStartDate);
                dbManager.AddParameters(5, "@EndDate", strEndDate);
                dbManager.AddParameters(6, "@Remark", strRemark);
                dbManager.AddParameters(7, "@CreatedBy", intCreateBy);
                dbManager.AddParameters(8, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                strSQL = "INSERT INTO hrmtraininghistory"
                         + " (emp_seq, "
                         + " CouseName,"
                         + " InstituteName,"
                         + " LocationName,"
                         + " StartDate,"
                         + " EndDate, "
                         + " Remark,"
                         + " CreatedWhen,"
                         + " CreatedBy,"
                         + " RecordStatus)"
                         + " VALUES"
                         + "( @emp_seq,"
                         + " @CouseName,"
                         + " @InstituteName,"
                         + " @LocationName,"
                         + " @StartDate,"
                         + " @EndDate,"
                         + " @Remark,"
                         + " GETDATE(),"
                         + " @CreatedBy,"
                         + " @RecordStatus)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(string strQueryString, string strCouseName, string strInstituteName, string strLocationName
            , Nullable<DateTime> strStartDate, Nullable<DateTime> strEndDate, string strRemark, int? intCreateBy, int? intTrainHisId)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@emp_seq", strQueryString);
                dbManager.AddParameters(1, "@CouseName", strCouseName);
                dbManager.AddParameters(2, "@InstituteName", strInstituteName);
                dbManager.AddParameters(3, "@LocationName", strLocationName);
                dbManager.AddParameters(4, "@StartDate", strStartDate);
                dbManager.AddParameters(5, "@EndDate", strEndDate);
                dbManager.AddParameters(6, "@Remark", strRemark);
                dbManager.AddParameters(7, "@UpdatedBy", intCreateBy);
                dbManager.AddParameters(8, "@TrainHisId", intTrainHisId);

                strSQL = "UPDATE hrmtraininghistory SET"
                         + " CouseName = @CouseName, "
                         + " InstituteName = @InstituteName,"
                         + " LocationName = @LocationName,"
                         + " StartDate = @StartDate,"
                         + " EndDate = @EndDate,"
                         + " UpdatedBy = @UpdatedBy,"
                         + " Remark = @Remark,"
                         + " UpdatedWhen = GETDATE()"
                         + " WHERE TrainHisId = @TrainHisId AND emp_seq = @emp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int? intCreateBy, int? intTrainHisId, string strQueryString)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@TrainHisId", intTrainHisId);
                dbManager.AddParameters(1, "@emp_seq", strQueryString);
                dbManager.AddParameters(2, "@UpdatedBy", intCreateBy);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                strSQL = " UPDATE hrmtraininghistory SET "
                         + " UpdatedBy = @UpdatedBy,"
                         + " UpdatedWhen = GETDATE(),"
                         + " RecordStatus = @RecordStatus"
                         + " WHERE TrainHisId = @TrainHisId AND emp_seq = @emp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool CheckValueSubmit(int? intTrainHisId)
        {
            string strSQL;

            bool result = true;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@TrainHisId", intTrainHisId);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT TrainHisId, emp_seq, StartDate, EndDate, CouseName "
                         + " , Institution, LocationName"
                         + " FROM  hrmtraininghistory"
                         + " WHERE RecordStatus = @RecordStatus";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true;
                    break;
                }
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return result;
        }

        public bool CheckValueDelete(int? intTrainHisId)
        {
            string strSQL;

            bool result = true;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@TrainHisId", intTrainHisId);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT *"
                         + " FROM  hrmtraininghistory"
                         + " WHERE RecordStatus = @RecordStatus AND TrainHisId = @TrainHisId";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true;
                    break;
                }
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return result;
        }

        public DataTable LoadInstitutes()
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT  training_ins_seq, training_ins_descr FROM  hrm_training_institutes"
                        + " WHERE record_status not like @status";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}